package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.SheJiShi;
import com.ysd.util.DBManager;

public class SheJiShiErDaoImpl {
	public List<SheJiShi> getSheJiShiList() {
		List<SheJiShi> list = new ArrayList<SheJiShi>();
		Statement st = null;
		ResultSet rs = null;

		Connection conn = DBManager.getConnection();
		try {
			st = conn.createStatement();
			// 3.SQL语句
			String sql = "select * from shejishi";
			// 4.执行SQL语句，返回结果集
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int sheJiShiid = rs.getInt("sheJiShiid");
				String sheJiShiName = rs.getString("sheJiShiName");
				String zhiCheng = rs.getString("zhiCheng");
				String sheJiShiTX = rs.getString("sheJiShiTX");
				SheJiShi sjs = new SheJiShi(sheJiShiid, sheJiShiName,

						zhiCheng, sheJiShiTX);
				list.add(sjs);// 把user对象存入到list中

			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}

	/**
	 * 根据id删除设计师
	 * 
	 * @param cId 要删除的设计师的id
	 * @return 数据库受影响的行数
	 */
	public int deletesheJiShiid(int cId) {
		return DBManager.executeUpdate("delete from shejishi where sheJiShiid=" + cId);

	}
	
	/**
	 * 批量删除
	 * @param ids
	 * @return
	 */
	public int deletesheJiShiids(String sheJiShiids) {
		String sql = "delete from shejishi where sheJiShiid in("+sheJiShiids+")";
		System.out.println(sql) ;
		return DBManager.executeUpdate(sql);
	}
	

	/**
	 * 添加设计师
	 * 
	 * @param sjs
	 * @return 数据库返回受影响行数
	 */
	public int addSheJiShi(SheJiShi sjs) {
		return DBManager.executeUpdate("insert into shejishi values(null,'" + sjs.getSheJiShiName() + "','"
				+ sjs.getZhiCheng() + "','" + sjs.getSheJiShiTX() + "')");
	}

	public List<SheJiShi> getSheJiShiByName(String name) {
		List<SheJiShi> sjsList = new ArrayList<SheJiShi>();

		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from shejishi where sheJiShiName='" + name + "'");
			while (rs.next()) {
				int sjsId = rs.getInt("sheJiShiid");
				String sjsName = rs.getString("sheJiShiName");
				String sjsZhiCheng = rs.getString("zhiCheng");
				String sjsTX = rs.getString("sheJiShiTX");
				SheJiShi sjs = new SheJiShi(sjsId, sjsName, sjsZhiCheng, sjsTX);
				sjsList.add(sjs);
			}
			return sjsList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}

	public SheJiShi getSheJiShiById(int sheId) {// 查到的结果集只有可能是一条,所以返回单个对象
		SheJiShi sjs = null;

		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;

		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from shejishi where sheJiShiid=" + sheId);
			while (rs.next()) {
				int sjsId = rs.getInt("sheJiShiid");
				String sjsName = rs.getString("sheJiShiName");
				String sjsZhiCheng = rs.getString("zhiCheng");
				String sjsTX = rs.getString("sheJiShiTX");
				sjs = new SheJiShi(sjsId, sjsName, sjsZhiCheng, sjsTX);// 创建设计师对象
			}
			return sjs;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}

	public int updateSheJiShiById(SheJiShi sjs) {
		String sql = "update shejishi set sheJiShiName='" + sjs.getSheJiShiName() + "'," + "zhiCheng='"
				+ sjs.getZhiCheng() + "'," + "sheJiShiTX='" + sjs.getSheJiShiTX() + "'" + " where sheJiShiid="
				+ sjs.getSheJiShiid();
		System.out.println(sql);
		return DBManager.executeUpdate(sql);
	}
	
	/**
	 * 分页查询
	 * @return
	 */
	public List<SheJiShi> getSheJiShiListByPage(int page,int size) {
		List<SheJiShi> list = new ArrayList<SheJiShi>();
		Statement st = null;
		ResultSet rs = null;
		
		Connection conn = DBManager.getConnection();
		try {
			st = conn.createStatement();
			
			String sql = "select * from shejishi limit "+(page-1)*size+","+size;
			System.out.println(sql);
			
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int sheJiShiid = rs.getInt("sheJiShiid");
				String sheJiShiName = rs.getString("sheJiShiName");
				String zhiCheng = rs.getString("zhiCheng");
				String sheJiShiTX = rs.getString("sheJiShiTX");
				SheJiShi sjs = new SheJiShi(sheJiShiid, sheJiShiName, zhiCheng, sheJiShiTX);
				list.add(sjs);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}

}
